﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using GMS.MainClasses;
using GMS.ServiceFunctions.Data;
using GMS.ServiceFunctions.FunctionForControls;
using System.Data.SqlClient;
using GMS.MainForm;
using System.Reflection;

namespace GMS.GroupsMenu
{
    public partial class GroupsMenu : ChildForms
    {
        ReturnData MRF = new ReturnData();
        FunctionsForDataGridView FFDGV = new FunctionsForDataGridView();
        FunctionForTreeView FFTV = new FunctionForTreeView();
        BindingSource bsGroups = new BindingSource();
        BindingSource bsMenus = new BindingSource();
        BindingSource bsSPPV = new BindingSource();
        public GroupsMenu()
        {
            InitializeComponent();

            ConnectionString = "server=pavel;uid=pavel;pwd=123456;database=GMSDatabase";

            StoredProcedures = new String[9];

            StoredProcedures[0] = "[SelectGroups]";
            StoredProcedures[1] = "[SelectMenuContent]";
            StoredProcedures[2] = "[AddMenuToGroup]";
            StoredProcedures[3] = "[SelectGroupIdOrMenuId]";
            StoredProcedures[4] = "[DeleteLinkGroupMenu]";
            StoredProcedures[5] = "[SelectStoredProcedureParameterValue]";
            StoredProcedures[6] = "[SelectFormsSP]";
            StoredProcedures[7] = "[AddRecordToFormSP]";
            StoredProcedures[8] = "[DeleteRecordItemSPValue]";
        }
        void LoadGroups()
        {
            bsGroups.DataSource = MRF.ReturnDataSetDB(ConnectionString, StoredProcedures[0]);
            bsGroups.DataMember = "table";
        }
        void LoadMenus()
        {
            bsMenus.DataSource = MRF.ReturnDataSetDB(ConnectionString, StoredProcedures[1]);
            bsMenus.DataMember = "table";
            FFTV.FillTreeView(treeView1, bsMenus, "IdItem", "IdParentItem", "CaptionItem");                 
        }
        void LoadSPPV(Int32 IdItem, Int32 IdGroup)
        {
            bsSPPV.DataSource = MRF.ReturnDataSetDB(ConnectionString, StoredProcedures[5]+" "+IdItem+","+IdGroup);
            bsSPPV.DataMember = "table";
        }

        private void GroupsMenu_Load(object sender, EventArgs e)
        {
            LoadGroups();
            dataGridView1.DataSource = bsGroups;
            LoadMenus();
        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            try
            {
                FFDGV.PaintGridRows(dataGridView1, e, dataGridView1.Columns["GroupStatus"].Index, -1, System.Drawing.Color.Red);
            }
            catch { }
        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            FFDGV.TextForGroupBox(groupBox1, dataGridView1, "Группы         ");
            if (checkBox1.Checked == true)
            {
                try
                {
                    String s = MakeStringValueFromTable(MRF.ReturnDataSetDB(ConnectionString, StoredProcedures[3] + "0," + Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value)).Tables[0]);
                    bsMenus.Filter = "[IdItem] in (" + s + ")";
                    LoadMenus();
                }
                catch 
                {
                    
                }
            }
            try
            { 
                LoadSPPV(Convert.ToInt32(treeView1.SelectedNode.Tag), Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value));
                dataGridView2.DataSource = bsSPPV;
            }
            catch { }
        }
        String MakeStringValueFromTable(DataTable Table)
        {
            String Value = " ";
            foreach (DataRow DR in Table.Rows)
            {
                Value = DR[0].ToString() + "," + Value;
            }
            Value = Value.Trim() + "0";

            return Value;        
        }

        private void contextMenuStrip2_Opening(object sender, CancelEventArgs e)
        {
            if (checkBox2.Checked == true)
            {
                e.Cancel = true;
            }
            AddToGroupToolStripMenuItem.Text = "Добавить в группу - " + dataGridView1.CurrentRow.Cells["Название группы"].Value.ToString();
            DeleteFromGrouptoolStripMenuItem2.Text = "Удалить из группы - " + dataGridView1.CurrentRow.Cells["Название группы"].Value.ToString();
        }

        private void textBox1_KeyUp(object sender, KeyEventArgs e)
        {
            bsGroups.Filter = "[Название группы] like '%"+textBox1.Text.Trim()+"%'";
            LoadGroups();
        }

        private void textBox2_KeyUp(object sender, KeyEventArgs e)
        {
            bsMenus.Filter = "[CaptionItem] like '%"+textBox2.Text.Trim()+"%'";
            LoadMenus();
        }

        private void AddToGroupToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                MRF.ExecuteSqlCommand(StoredProcedures[2] + " " + Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value) + "," + treeView1.SelectedNode.Tag, ConnectionString);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString(),"Ошибка!!!",MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }

        private void checkBox1_Click(object sender, EventArgs e)
        {
            bsMenus.Filter = String.Empty;
            LoadMenus();
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (checkBox2.Checked == true)
            {
                try
                {
                    String s = MakeStringValueFromTable(MRF.ReturnDataSetDB(ConnectionString, StoredProcedures[3] + "1," + treeView1.SelectedNode.Tag).Tables[0]);
                    bsGroups.Filter = "[IdGroup] in (" + s + ")";
                    LoadGroups();
                }
                catch
                { }
            }
            try
            {
                LoadSPPV(Convert.ToInt32(treeView1.SelectedNode.Tag), Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value));
                dataGridView2.DataSource = bsSPPV;
            }
            catch { }
        }

        private void checkBox2_Click(object sender, EventArgs e)
        {
            bsGroups.Filter = String.Empty;
            LoadGroups();
        }

        private void DeleteFromGrouptoolStripMenuItem2_Click(object sender, EventArgs e)
        {
            try
            {
                MRF.ExecuteSqlCommand(StoredProcedures[4] + " " + treeView1.SelectedNode.Tag + "," + Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value), ConnectionString);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(),"Ошибка!!!",MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }

        private void LoadParametersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MRF.ExecuteSqlCommand(StoredProcedures[8] + " " + treeView1.SelectedNode.Tag + "," + Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value) , ConnectionString);
            foreach (String s in GetSPForms(Convert.ToInt32(treeView1.SelectedNode.Tag)))
            {
                for (Int32 i = 0; i < GetSPParameters(ConnectionString, s).Length/2; i++)
                {
                    String[,] ValTypes = GetSPParameters(ConnectionString, s);
                    MRF.ExecuteSqlCommand(StoredProcedures[7] + " " + treeView1.SelectedNode.Tag + "," + Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value) + ",'" + s + "','" + ValTypes[i, 0].ToString() + "','" + ValTypes[i, 1].ToString() + "','null'", ConnectionString);

                }
            }
            LoadSPPV(Convert.ToInt32(treeView1.SelectedNode.Tag), Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value));
        }
        String[,] GetSPParameters(String ConnectionString, String ProcedureName)
        {
            String[,] Parameters = null;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(ProcedureName, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    try
                    {
                        conn.Open();
                        SqlCommandBuilder.DeriveParameters(cmd); 
                        Parameters = new String[cmd.Parameters.Count, 2];
                        for (Int32 i = 0; i < cmd.Parameters.Count; i++)
                        {
                            Parameters[i, 0] = cmd.Parameters[i].ParameterName;
                            Parameters[i, 1] = cmd.Parameters[i].DbType.ToString();
                        }

                    }
                    catch
                    {

                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                            conn.Close();
                    }
                }
            }
            return Parameters;
        }
        String[] GetSPForms(Int32 IdForm)
        {
            DataTable DT = MRF.ReturnDataSetDB(ConnectionString, StoredProcedures[6] + " " + IdForm).Tables[0];
            String[] SP = new String[DT.Rows.Count];

            for (Int32 i = 0; i < DT.Rows.Count; i++)
            {
                SP[i] = DT.Rows[i][2].ToString();
            }
            return SP;
        }

        private void dataGridView2_SelectionChanged(object sender, EventArgs e)
        {
            FFDGV.TextForGroupBox(groupBox3, dataGridView2, "Параметры         ");
        }

        private void EditValueToolStripMenuItem_Click(object sender, EventArgs e)
        {
            EditParameterValues EPV = new EditParameterValues();
            EPV.ConnectionString = ConnectionString;
            EPV.IdItem = Convert.ToInt32(treeView1.SelectedNode.Tag);
            EPV.IdGroup = Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value);
            EPV.SP = dataGridView2.CurrentRow.Cells["Хранимая процедура"].Value.ToString();
            EPV.Parameter = dataGridView2.CurrentRow.Cells["Параметр"].Value.ToString();
            EPV.ParameterType = dataGridView2.CurrentRow.Cells["Тип параметра"].Value.ToString();
            EPV.Value = dataGridView2.CurrentRow.Cells["Значение"].Value.ToString();
            //EPV.MdiParent = MRF;
            EPV.Show();
        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            MRF.ExecuteSqlCommand(StoredProcedures[8] + " " + treeView1.SelectedNode.Tag + "," + Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value) + ",'" + dataGridView2.CurrentRow.Cells["Хранимая процедура"].Value.ToString() + "','" + dataGridView2.CurrentRow.Cells["Параметр"].Value.ToString() + "'", ConnectionString);
            LoadSPPV(Convert.ToInt32(treeView1.SelectedNode.Tag), Convert.ToInt32(dataGridView1.CurrentRow.Cells["IdGroup"].Value));
        }

        private void textBox3_KeyUp(object sender, KeyEventArgs e)
        {
            bsSPPV.Filter = "[Хранимая процедура] like '%"+textBox3.Text.Trim()+"%'";
        }

        private void toolStripButton6_Click(object sender, EventArgs e)
        {
            AbouGroupsMenu AGM = new AbouGroupsMenu();
            AGM.Show();
        }
        void test()
        {
            /*
            Type t = this.GetType();//ParentForm.GetType();
            String name = t.Assembly.GetName().Name + ".dll";
            DataTable DT;
            DT = MRF.ReturnDataSetDB(ConnectionString, "select IdForm from Forms where [Assembly] = '" + name + "'").Tables[0];
            Int32 idform = Convert.ToInt32(DT.Rows[0][0]);*/

            String[,] param = new String[6,2];
            param[0, 0] = ""; param[0, 1] = "";


            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                String SP = "[AddDatabaseUser] '1','2','3','4','5'";
                using (SqlCommand cm = new SqlCommand(SP, conn))
                {
                    conn.Open();
                    cm.CommandType = CommandType.StoredProcedure;

                    
                    cm.Prepare();

                    String[] pa = new String[cm.Parameters.Count];
                    for (Int32 j = 0; j < cm.Parameters.Count; j++)
                    {
                        pa[j] = cm.Parameters[j].Value.ToString();
                    }

                   String val = cm.Parameters[1].Value.ToString();

                    String[,] para = GetSPParameters(ConnectionString, SP);

                    for (Int32 i = 0; i < para.Length / 2; i++)
                    {

                    }
                    conn.Close();
                }
            }


        }

        private void button1_Click(object sender, EventArgs e)
        {
//            test();
            object[] val = new object[5];
            val[0] = "test";
            val[1] = "test1";
            val[2] = "test2";
            SqlCommand c = GenerateCommand("[AddForm]",val);
        }

        public  SqlCommand GenerateCommand(String SP, object[] values)
        {

            SqlParameter prm;

            string SPName = SP;
            SqlConnection con = new SqlConnection(ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand(SPName, con) { CommandType = CommandType.StoredProcedure };

            SqlCommandBuilder.DeriveParameters(cmd);
            String[,] prms = GetSPParameters(ConnectionString, SPName);

            //ParameterInfo[] prms = method.GetParameters(); //Тут имена параметров

            for (int i = 0; i < prms.Length; i++)
            {
                i = i + 1;
                prm = cmd.Parameters.AddWithValue(prms[i,0], values[i]);
                //if (prms[i,0].IsOut) prm.Direction = ParameterDirection.Output;
            }

            //Тут вызов некоей универсальной функции проверки. Передаем ей имя ХП,
            //массив имен параметров, массив значений параметров. Можно при желании слепить структуру.
            VerifyAccess(SPName.ToString(), prms.ToString(), values.ToString());

            return cmd;
        }
        void VerifyAccess(String SPName, String Parameter, String Value)
        {
            String ValidValue;
            ValidValue = MRF.ReturnDataSetDB(ConnectionString, "SELECT [Value]  FROM [GMSDatabase].[dbo].[StoredProcedureParameterValue] where [StoredProcedure] = '" + SPName + "' and [Parameter]='" + Parameter + "'").Tables[0].Rows[0][0].ToString();
            if (ValidValue != Value)
                MessageBox.Show("dd");
        }

    }
}
